package com.bss.dao;

import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.bss.beans.Attendent;
import com.bss.beans.CheckInOut;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.bss.beans.MaterialAllocation;
import com.bss.beans.UtensilAllocation;
import com.bss.command.CheckInOutCommand;
import com.bss.framework.FrontController;

import com.bss.utilities.DateUtilities;

public class CheckInOutDao implements Runnable {

	public static ArrayList<Attendent> attendentList = null;

	public static String attendentJsp = null;

	static {
		try {
			updateAttendentList();
		} catch (Exception ee) {

		}
	}

	public static int FindMaxTransactionId() throws Exception {
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int patientId = 0;

		String insertString = "select max(transactionId) from check_in_out";

		conn = ConnectionDao.getConnection1();

		try {
			Ps = conn.prepareStatement(insertString);
			Rs = Ps.executeQuery();

			while (Rs.next()) {
				patientId = Rs.getInt(1);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return patientId;
	}

	public static boolean checkin(CheckInOut checkinout) throws Exception {
		Connection conn;
		PreparedStatement Ps;
		String insertString = "insert into check_in_out(transactionId,noOfMale,noOfFemale,noOfChild,patientId,checkInTime,townOfLastDeparture,depositAmount,dateOfStay,extDateOfStay) values(?,?,?,?,?,?,?,?,?,?)";

		conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(insertString);

			Ps.setInt(1, FindMaxTransactionId() + 1);
			Ps.setInt(2, checkinout.getINoOfMale());
			Ps.setInt(3, checkinout.getINoOfFemale());
			Ps.setInt(4, checkinout.getINoOfChild());
			Ps.setInt(5, checkinout.getIPatientId());
			Ps.setTimestamp(6, DateUtilities.convertToTimestamp(checkinout
					.getCalCheckInTime()));
			Ps.setString(7, checkinout.getSLastDepTown());
			Ps.setInt(8, checkinout.getIDepositAmount());
			Ps.setTimestamp(9, DateUtilities.convertToTimestamp(checkinout
					.getSDateOfStay()));
			Ps.setTimestamp(10, DateUtilities.convertToTimestamp(checkinout
					.getSExtDateOfStay()));

			Ps.executeUpdate();
			
			// AUDIT
			FrontController.audit("CheckedIn Patient, ID: "+ checkinout.getIPatientId() + ", Deposit: " + checkinout.getIDepositAmount() );

		} finally {
			ConnectionDao.closeConnection(conn);

		}

		return true;
	}

	public static ArrayList<Integer> loadCheckedInPatientId() throws Exception {
		ResultSet Rs = null;
		int user;
		ArrayList<Integer> userlist = new ArrayList<Integer>();
		String selectQuerry = "select distinct(patientId) from check_in_out where checkOutTime is null";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				user = Rs.getInt(1);
				userlist.add(user);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return userlist;

	}

	public static int findLastTransactionId(int patientId) throws Exception {
		ResultSet Rs = null;
		int user = 0;
		String selectQuerry = "SELECT transactionId FROM check_in_out where patientId=? and checkouttime is null and checkintime=(select max(checkintime) from check_in_out where patientid=?)";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);
			Ps.setInt(2, patientId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				user = Rs.getInt(1);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return user;

	}

	public static int findLastUsedTransactionId(int patientId) throws Exception {
		ResultSet Rs = null;
		int user = 0;
		String selectQuerry = "SELECT transactionId FROM check_in_out where patientId=? and checkintime=(select max(checkintime) from check_in_out where patientid=?)";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);
			Ps.setInt(2, patientId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				user = Rs.getInt(1);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return user;

	}

	public static boolean canPatientCheckOut(int patientId) throws Exception {
		ResultSet Rs = null;

		String selectQuerry = "SELECT count(*) FROM check_in_out where patientId=? and materialsAllocationId is null";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);
			Rs = Ps.executeQuery();

			if (Rs.next()) {
				if (Rs.getInt(1) == 0) {
					ConnectionDao.closeConnection(conn);
					return false;
				}
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return true;

	}

	public synchronized static boolean AddNewAttendent(Attendent attendent)
			throws Exception {
		Connection conn;
		PreparedStatement Ps;
		String insertString = "insert into attendent_record(attendentId,patientId,Name,relationship,transactionId,idType,idNumber,isPresent) values(?,?,?,?,?,?,?,?)";

		conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(insertString);

			Ps.setInt(1, attendent.getIAttendentUId());
			Ps.setInt(2, attendent.getIPatientId());
			Ps.setString(3, attendent.getSName());
			Ps.setString(4, attendent.getSRelationship());
			Ps.setInt(5, attendent.getITransactionId());
			Ps.setString(6, attendent.getSIdType());
			Ps.setString(7, attendent.getSIdNo());
			Ps.setBoolean(8, true);

			Ps.executeUpdate();

		} finally {
			ConnectionDao.closeConnection(conn);

		}

		updateAttendentList();

		return true;
	}

	protected static void updateAttendentList() {
		new Thread(new CheckInOutDao()).start();
	}

	public static int FindMaxAttendentId() throws Exception {
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int patientId = 0;

		String insertString = "select max(attendentId) from attendent_record";

		conn = ConnectionDao.getConnection1();

		try {
			Ps = conn.prepareStatement(insertString);
			Rs = Ps.executeQuery();

			while (Rs.next()) {
				patientId = Rs.getInt(1);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}
		return patientId;
	}

	public static int setCheckoutDate(String date, int patientId)
			throws Exception {
		Connection conn;
		PreparedStatement Ps;

		conn = ConnectionDao.getConnection1();

		try {

			String strSelect = "update check_in_out set checkOutTime=? where patientId=? and checkOutTime is null";

			Ps = conn.prepareStatement(strSelect);

			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date));
			Ps.setInt(2, patientId);

			Ps.executeUpdate();
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return 1;
	}

	public static int FindMaxMaterialAllocationId() throws Exception {
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int patientId = 0;

		String insertString = "select max(allocationId) from materialallocation";

		conn = ConnectionDao.getConnection1();

		try {
			Ps = conn.prepareStatement(insertString);
			Rs = Ps.executeQuery();

			while (Rs.next()) {
				patientId = Rs.getInt(1);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}
		return patientId;
	}

	public static boolean AllocateMaterial(MaterialAllocation materialAllocation)
			throws Exception {
		com.bss.framework.FrontController.trace("IN DB Utensil setting..........");
		Connection conn;
		PreparedStatement Ps;
		String insertString = "insert into materialallocation values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(insertString);
			Ps.setInt(1, materialAllocation.getIAllocationId());
			Ps.setInt(2, materialAllocation.getINoOfBS());
			Ps.setInt(3, materialAllocation.getINoOfWBS());
			Ps.setInt(4, materialAllocation.getINoOfMN());
			Ps.setInt(5, materialAllocation.getINoOfDMN());
			Ps.setInt(6, materialAllocation.getINoOfBK());
			Ps.setInt(7, materialAllocation.getINoOfMG());
			Ps.setInt(8, materialAllocation.getINoOfLK());
			Ps.setBoolean(9, materialAllocation.getReturned());
			Ps.setInt(10, materialAllocation.getINoOfMlkTkt());
			Ps.setInt(11, materialAllocation.getIOthersVal1());
			Ps.setInt(12, materialAllocation.getIOthersVal2());
			Ps.setString(13, materialAllocation.getSOthersType1());
			Ps.setString(14, materialAllocation.getSOthersType2());

			Ps.executeUpdate();

			com.bss.framework.FrontController.trace("DB Utensil set>>>>>");

		} finally {
			ConnectionDao.closeConnection(conn);

		}

		return true;
	}

	public static boolean setMaterials(int materialAllocationId, int patientId)
			throws Exception {
		
		Connection conn;
		PreparedStatement Ps;
		int noofrows = 0;
		conn = ConnectionDao.getConnection1();

		try {

			String strSelect = "update check_in_out set materialsAllocationId=? where patientId=? and checkOutTime is null";

			Ps = conn.prepareStatement(strSelect);

			Ps.setInt(1, materialAllocationId);
			Ps.setInt(2, patientId);

			noofrows = Ps.executeUpdate();
		} finally {
			ConnectionDao.closeConnection(conn);
		}
		com.bss.framework.FrontController.trace("setted the allocated material   ...noofrows:"
				+ noofrows);
		return true;
	}

	public static int selectMaterialsAllocated(
			MaterialAllocation materialAllocation, int patientId)
			throws Exception {
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int allocationId;

		String insertString = "select * from materialallocation where allocationId=?";

		conn = ConnectionDao.getConnection1();

		try {
			Ps = conn.prepareStatement(insertString);
			allocationId = getMaterialAllocationId(patientId);

			if (allocationId == 0) {
				ConnectionDao.closeConnection(conn);
				return 0;
			}
			Ps.setInt(1, allocationId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {
				materialAllocation.setIAllocationId(Rs.getInt(1));
				materialAllocation.setINoOfBS(Rs.getInt(2));
				materialAllocation.setINoOfWBS(Rs.getInt(3));
				materialAllocation.setINoOfMN(Rs.getInt(4));
				materialAllocation.setINoOfDMN(Rs.getInt(5));
				materialAllocation.setINoOfBK(Rs.getInt(6));
				materialAllocation.setINoOfMG(Rs.getInt(7));
				materialAllocation.setINoOfLK(Rs.getInt(8));
				materialAllocation.setReturned(Rs.getBoolean(9));
				materialAllocation.setINoOfMlkTkt(Rs.getInt(10));
				materialAllocation.setIOthersVal1(Rs.getInt(11));
				materialAllocation.setIOthersVal2(Rs.getInt(12));
				materialAllocation.setSOthersType1(Rs.getString(13));
				materialAllocation.setSOthersType2(Rs.getString(14));
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}
		return allocationId;
	}

	public static int getMaterialAllocationId(int patientId) throws Exception {
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int id = 0;

		String insertString = "select materialsAllocationId from check_in_out where patientId=? and checkOutTime is null and materialsAllocationId is not null";

		conn = ConnectionDao.getConnection1();

		try {
			Ps = conn.prepareStatement(insertString);
			Ps.setInt(1, patientId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {
				id = Rs.getInt(1);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}
		return id;
	}

	public static boolean returnMaterials(int allocationId, int patientId)
			throws Exception {
		Connection conn;
		PreparedStatement Ps;

		conn = ConnectionDao.getConnection1();

		try {

			String strSelect = "update check_in_out set materialsAllocationId=null where patientId=? and checkOutTime is null";

			Ps = conn.prepareStatement(strSelect);

			Ps.setInt(1, patientId);

			Ps.executeUpdate();
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return true;
	}

	public static int FindMaxutensilAllocationId() throws Exception {
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int patientId = 0;

		String insertString = "select max(utensilAllocationId) from utensil_allocation";

		conn = ConnectionDao.getConnection1();

		try {
			Ps = conn.prepareStatement(insertString);
			Rs = Ps.executeQuery();

			while (Rs.next()) {
				patientId = Rs.getInt(1);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}
		return patientId;
	}

	public static boolean allocateNewUtensils(UtensilAllocation utensil)
			throws Exception {
		Connection conn;
		PreparedStatement Ps;
		String insertString = "insert into utensil_allocation(utensilAllocationId,patientId,allocationType,amountDeposited,allocationDate) values(?,?,?,?,?)";

		conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(insertString);

			Ps.setInt(1, utensil.getIAllocationId());
			Ps.setInt(2, utensil.getIPatientId());
			Ps.setInt(3, utensil.getIAllocationType());
			Ps.setInt(4, utensil.getSAmountDeposited());
			Ps.setTimestamp(5, DateUtilities.convertToTimestamp(utensil
					.getCalAllocationDate()));

			Ps.executeUpdate();

		} finally {
			ConnectionDao.closeConnection(conn);

		}

		return true;
	}

	public static boolean canPatientAllocateUtensil(int patientId)
			throws Exception {
		ResultSet Rs = null;

		String selectQuerry = "SELECT count(*) FROM utensil_allocation where patientId=?  and returnDate is null";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);
			Rs = Ps.executeQuery();

			if (Rs.next()) {
				if (Rs.getInt(1) == 0) {
					ConnectionDao.closeConnection(conn);
					return true;
				}
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return false;

	}

	public static UtensilAllocation PatientAllocatedUtensil(int patientId)
			throws Exception {
		ResultSet Rs = null;

		String selectQuerry = "SELECT * FROM utensil_allocation where patientId=? and returnDate is null";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();
		UtensilAllocation utensil = new UtensilAllocation();
		int isAllocated = 0;
		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {
				utensil.setIAllocationId(Rs.getInt(1));
				utensil.setIPatientId(Rs.getInt(2));
				utensil.setIAllocationType(Rs.getInt(3));
				utensil.setSAmountDeposited(Rs.getInt(4));
				isAllocated = 1;
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}
		if (isAllocated == 1)
			return utensil;
		else
			return null;

	}

	public static boolean canPatientDeAllocateUtensil(int patientId)
			throws Exception {
		ResultSet Rs = null;

		String selectQuerry = "SELECT count(*) FROM utensil_allocation where patientId=? and returnDate is null";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);
			Rs = Ps.executeQuery();

			if (Rs.next()) {
				if (Rs.getInt(1) == 0) {
					ConnectionDao.closeConnection(conn);
					
					return false;
				}
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}
		
		return true;

	}

	public static int findUtensilAmount(int patientId) throws Exception {
		ResultSet Rs = null;
		int amount = 0;
		String selectQuerry = "SELECT amountDeposited FROM utensil_allocation where patientId=? and returnDate is null";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);
			Rs = Ps.executeQuery();

			if (Rs.next()) {
				amount = Rs.getInt(1);
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return amount;

	}

	public static int setUtensilDeallocateDate(String date, int patientId)
			throws Exception {
		Connection conn;
		PreparedStatement Ps;

		conn = ConnectionDao.getConnection1();

		try {

			String strSelect = "update utensil_allocation set returnDate=? where patientId=? and returnDate is null";

			Ps = conn.prepareStatement(strSelect);

			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date));
			Ps.setInt(2, patientId);

			Ps.executeUpdate();
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return 1;
	}

	public static ArrayList<CheckInOut> loadCheckedInRecords() throws Exception {
		ResultSet Rs = null;
		int user;
		ArrayList<CheckInOut> list = new ArrayList<CheckInOut>();
		String selectQuerry = "select * from check_in_out where checkOutTime is null";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				CheckInOut record = new CheckInOut();

				record.setITransactionId(Rs.getInt(1));
				record.setCalCheckInTime(Rs.getNString(6));
				record.setIDepositAmount(Rs.getInt(10));
				record.setINoOfChild(Rs.getInt(4));
				record.setINoOfFemale(Rs.getInt(3));
				record.setINoOfMale(Rs.getInt(2));
				record.setIPatientId(Rs.getInt(5));
				record.setMaterialAllocation(Rs.getInt(9));
				record.setSLastDepTown(Rs.getString(8));

				list.add(record);

			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return list;

	}

	public static CheckInOut loadCheckedInPatient(int transactionId)
			throws Exception {
		ResultSet Rs = null;
		int user;

		String selectQuerry = "select * from check_in_out where transactionId=?";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();
		CheckInOut record = new CheckInOut();
		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, transactionId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				record.setITransactionId(Rs.getInt(1));
				record.setCalCheckInTime(Rs.getString(6));
				record.setIDepositAmount(Rs.getInt(10));
				record.setINoOfChild(Rs.getInt(4));
				record.setINoOfFemale(Rs.getInt(3));
				record.setINoOfMale(Rs.getInt(2));
				record.setIPatientId(Rs.getInt(5));
				record.setMaterialAllocation(Rs.getInt(9));
				record.setSLastDepTown(Rs.getString(8));
				if (Rs.getTimestamp(11) != null) {
					record.setSDateOfStay(Rs.getTimestamp(11).toString());
				}
				if (Rs.getTimestamp(12) != null) {
					record.setSExtDateOfStay(Rs.getTimestamp(12).toString());
				}
				if (Rs.getTimestamp(6) != null) {
					record.setCalCheckInTime(Rs.getTimestamp(6).toString());
				}
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return record;

	}

	public static String findDateOfStay(int transactionId) throws Exception {
		String date = "";

		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;

		String insertString = "select dateOfStay from check_in_out where transactionId=?";

		conn = ConnectionDao.getConnection1();

		try {
			Ps = conn.prepareStatement(insertString);
			Ps.setInt(1, transactionId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {
				if (Rs.getTimestamp(1) != null) {
					date = Rs.getTimestamp(1).toString();
				} else {
					date = "";
				}
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return date;
	}

	public static ArrayList<Integer> findAllTransactionId(int patientId)
			throws Exception {
		ResultSet Rs = null;
		ArrayList<Integer> transactionIds = new ArrayList<Integer>();
		int user = 0;
		String selectQuerry = "SELECT transactionId FROM check_in_out where patientId=? ";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				user = Rs.getInt(1);
				transactionIds.add(user);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return transactionIds;

	}

	public static int findPatientId(int transactionId) throws Exception {
		ResultSet Rs = null;
		int patientId = 0;
		String selectQuerry = "SELECT patientId FROM check_in_out where transactionId=? ";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, transactionId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				patientId = Rs.getInt(1);

			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}
		return patientId;
	}

	public static ArrayList<Integer> loadCheckedInTransactionId()
			throws Exception {
		ResultSet Rs = null;
		int user;
		ArrayList<Integer> userlist = new ArrayList<Integer>();
		String selectQuerry = "select distinct(transactionId) from check_in_out where checkOutTime is null";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				user = Rs.getInt(1);
				userlist.add(user);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return userlist;

	}

	public static String findCheckedinIdRange(String date1, String date2)
			throws Exception {
		String transactionIdRange = "";
		ResultSet Rs = null;

		String selectQuerry = "select max(transactionId), min(transactionId)  from check_in_out where checkInTime >= ? and checkInTime < ?";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date1));
			Ps.setTimestamp(2, DateUtilities.convertToTimestamp(date2));
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				transactionIdRange = Rs.getInt(1) + "-" + Rs.getInt(2);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return transactionIdRange;
	}

	public static int findCheckedinDepsoit(String date1, String date2)
			throws Exception {
		int amount = 0;
		ResultSet Rs = null;

		String selectQuerry = "select sum(depositAmount)  from check_in_out where transactionId >= (select min(transactionId)  from check_in_out where checkInTime >= ? and checkInTime < ?) and transactionId <= (select max(transactionId)  from check_in_out where checkInTime >= ? and checkInTime < ?)";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date1));
			Ps.setTimestamp(2, DateUtilities.convertToTimestamp(date2));
			Ps.setTimestamp(3, DateUtilities.convertToTimestamp(date1));
			Ps.setTimestamp(4, DateUtilities.convertToTimestamp(date2));
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				amount = Rs.getInt(1);

			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return amount;
	}

	public static void findUtensilAllocation(String date1, String date2,
			ArrayList<Integer> patientId, ArrayList<Integer> amount)
			throws Exception {

		ResultSet Rs = null;

		String selectQuerry = "select patientId, amountDeposited  from utensil_allocation where allocationDate >= ? and allocationDate < ? order by allocationDate";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date1));
			Ps.setTimestamp(2, DateUtilities.convertToTimestamp(date2));
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				patientId.add(CheckInOutCommand.findLastTransactionId(Rs
						.getInt(1)));
				amount.add(Rs.getInt(2));
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

	}

	public static void findCheckoutList(String date1, String date2,
			ArrayList<Integer> transactionId, ArrayList<Integer> amount)
			throws Exception {

		ResultSet Rs = null;

		String selectQuerry = "select transactionId, depositAmount  from check_in_out where checkOutTime >= ? and checkOutTime < ? and checkOutTime is not null order by checkOutTime";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date1));
			Ps.setTimestamp(2, DateUtilities.convertToTimestamp(date2));
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				transactionId.add(Rs.getInt(1));
				amount.add(Rs.getInt(2));
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

	}

	public static void findUtensilDeAllocation(String date1, String date2,
			ArrayList<Integer> patientId, ArrayList<Integer> amount)
			throws Exception {

		ResultSet Rs = null;

		String selectQuerry = "select patientId, amountDeposited  from utensil_allocation where returnDate >= ? and returnDate < ? order by returnDate";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date1));
			Ps.setTimestamp(2, DateUtilities.convertToTimestamp(date2));
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				patientId.add(CheckInOutDao.findLastUsedTransactionId(Rs
						.getInt(1)));
				amount.add(Rs.getInt(2));
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

	}

	public static ArrayList<Attendent> getAttendentList(int transactionId)
			throws Exception {
		ArrayList<Attendent> attendents = new ArrayList<Attendent>();
		Attendent attendent;
		ResultSet Rs = null;
		String selectQuerry = "select attendentId,patientId,Name,relationship,transactionId,idType,idNumber,isPresent  from attendent_record where transactionId = ?";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {
			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, transactionId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				attendent = new Attendent();

				attendent.setIAttendentUId(Rs.getInt(1));
				attendent.setIPatientId(Rs.getInt(2));
				attendent.setSName(Rs.getString(3));
				attendent.setSRelationship(Rs.getString(4));
				attendent.setBIsPresent(Rs.getBoolean(8));

				attendents.add(attendent);

			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}
		return attendents;
	}

	public static boolean changeIsPresent(int attendentId, Boolean isPresent)
			throws Exception {
		String selectQuerry = "update attendent_record set isPresent = ?  where attendentId = ?";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();
		try {
			Ps = conn.prepareStatement(selectQuerry);
			Ps.setBoolean(1, isPresent);
			Ps.setInt(2, attendentId);
			Ps.executeUpdate();
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return true;
	}

	public static int findSecurityAmount(int patientId) throws Exception {
		ResultSet Rs = null;
		int amount = 0;
		String selectQuerry = "SELECT depositAmount FROM check_in_out where patientId=? and transactionId = (select max(transactionId) from check_in_out where patientId=?) ";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);
			Ps.setInt(2, patientId);
			Rs = Ps.executeQuery();
			if (Rs.next()) {
				amount = Rs.getInt(1);
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return amount;

	}

	public static int countNoOfPatientsIn() throws Exception {
		ResultSet Rs = null;
		int count = 0;
		String selectQuerry = "SELECT count(*) FROM check_in_out where checkOutTime is null	";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);

			Rs = Ps.executeQuery();
			if (Rs.next()) {
				count = Rs.getInt(1);
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}
		return count;

	}

	public static int makeIsPresentFalse(int transactionId) throws Exception {
		String selectQuerry = "update attendent_record set isPresent = ?  where transactionId = ?";
		PreparedStatement Ps;
		int recModified = 0;
		Connection conn = ConnectionDao.getConnection1();
		try {
			Ps = conn.prepareStatement(selectQuerry);
			Ps.setBoolean(1, false);
			Ps.setInt(2, transactionId);
			recModified = Ps.executeUpdate();
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return recModified;
	}

	public static int countNoOfAttendentsIn() throws Exception {
		ResultSet Rs = null;
		int count = 0;
		String selectQuerry = "SELECT count(distinct(Name)) FROM attendent_record where isPresent is true	";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);

			Rs = Ps.executeQuery();
			if (Rs.next()) {
				count = Rs.getInt(1);
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}
		return count;
	}

	
	public synchronized static ArrayList<Attendent> loadAttendentList() throws Exception {
		
		ArrayList<Attendent> list = new ArrayList<Attendent>();
		Attendent attendent;
		ResultSet Rs = null;
		// String
		// selectQuerry="SELECT * FROM attendent_record where Name in (select distinct(Name) from attendent_record) order by Name";
		String selectQuerry = "SELECT * FROM attendent_record order by Name";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			
			Rs = Ps.executeQuery();
			while (Rs.next()) {
				attendent = new Attendent();
				attendent.setIAttendentUId(Rs.getInt(1));
				attendent.setIPatientId(Rs.getInt(2));
				attendent.setITransactionId(Rs.getInt(5));
				attendent.setSIdNo(Rs.getString(7));
				attendent.setSIdType(Rs.getString(6));
				attendent.setSName(Rs.getString(3));
				attendent.setSRelationship(Rs.getString(4));
				attendent.setBIsPresent(Rs.getBoolean(8));
				if(!list.contains(attendent)) {
					list.add(attendent);
					//System.out.println("Adding... ");
				}
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}
		attendentList = list;
		return list;
	}
	
	
	
	public synchronized static ArrayList<Attendent> getAttendentList()
			throws Exception {
		System.out.println(attendentList == null ? 0: attendentList.size());
		if (attendentList != null)
			return attendentList;
		return loadAttendentList();
	}

	public static Boolean canPatAllocMat(int transactionId) throws Exception {
		ResultSet Rs = null;
		Boolean status = false;
		String selectQuerry = "SELECT materialsAllocationId FROM check_in_out where transactionId = ?";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();
		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, transactionId);
			Rs = Ps.executeQuery();
			while (Rs.next()) {
				if (Rs.getInt(1) == 0) {
					status = true;
				}
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}
		return status;

	}

	public static ArrayList<Integer> getAttendentCount(int transactionId)
			throws Exception {
		ArrayList<Integer> count = new ArrayList<Integer>();
		ResultSet Rs = null;
		String selectQuerry = "SELECT noOfMale, noOfFemale, noOfChild FROM check_in_out where transactionId = ?";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();
		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, transactionId);
			Rs = Ps.executeQuery();
			while (Rs.next()) {
				if (Rs.getInt(1) == 0) {
					count.add(Rs.getInt(1));
					count.add(Rs.getInt(2));
					count.add(Rs.getInt(3));
				}
			}
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return count;
	}

	public static int setAttendentCount(int transactionId,
			ArrayList<Integer> count) throws Exception

	{

		Connection conn;
		PreparedStatement Ps;
		conn = ConnectionDao.getConnection1();
		try {
			String strSelect = "update check_in_out set noOfMale = ?, noOfFemale = ?, noOfChild = ?  where transactionId=? ";

			Ps = conn.prepareStatement(strSelect);
			Ps.setInt(1, count.get(0));
			Ps.setInt(2, count.get(1));
			Ps.setInt(3, count.get(2));
			Ps.setInt(4, transactionId);
			Ps.executeUpdate();
		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return 1;
	}

	public void run() {
		// TODO Auto-generated method stub
		try {
			attendentList = loadAttendentList();
			//attendentJsp = generateAttendentJsp(attendentList);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static String generateAttendentJsp(ArrayList<Attendent> attendentList) {

		if (attendentJsp != null)
			return attendentJsp;

		StringBuilder sb = new StringBuilder();

		// ///

		for (int i = 0; i < attendentList.size(); i++) {
			String value = attendentList.get(i).getSName() + ":"
					+ attendentList.get(i).getSRelationship() + ":"
					+ attendentList.get(i).getSIdType() + ":"
					+ attendentList.get(i).getSIdNo();
			sb.append("<tr>");
			sb.append("<td width='85' align='center'>");

			sb.append("<a href=\"javascript:post_value('" + value + "')\"> "
					+ i + " </a>");
			sb.append("</td>");
			sb.append("<td align='center'>"
					+ attendentList.get(i).getSName().toUpperCase() + "</td>");
			sb.append("</tr>");
			// ///
		}
		attendentJsp = sb.toString();
		return attendentJsp;

	}

}